package com.db.dbdocument.generateDB.service.impl;


import com.db.dbdocument.generateDB.common.DataSoreceConnect;
import com.db.dbdocument.generateDB.common.JdbcConnect;
import com.db.dbdocument.generateDB.service.IDbInfoService;
import com.db.dbdocument.generateDB.util.StrUtils;
import com.db.dbdocument.generateDB.vo.DbInfoVo;
import com.db.dbdocument.generateDB.vo.DbTableDocumentVo;
import com.db.dbdocument.generateDB.vo.DbTableInfoVo;
import com.db.dbdocument.generateDB.vo.DbTableStructureInfoVo;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 获取数据库信息 服务实现类
 */
@Service
public class DbInfoServiceImpl implements IDbInfoService {

    @Value("${db.properties}")
    private String dbProperties;


    //1. 获取数据库连接
    @Override
    public List<Map<String, Object>> getDbConnect() {

        return null;
    }
    //2. 获取数据库信息
    @Override
    public DbInfoVo getDbInfo() throws SQLException, ClassNotFoundException {
        //获取当前连接的数据库名称[database()] 版本号[version()]
        String dbNameSql  = "select database() as dbName,version() as version;";
        List<Map<String, Object>> dbList  = JdbcConnect.mysqlDbQuery(dbNameSql);
        String dbName = dbList.get(0).getOrDefault("dbName","").toString();
        String version = dbList.get(0).getOrDefault("version","").toString();
        DbInfoVo dbInfoVo = new DbInfoVo();
        dbInfoVo.setDbName(dbName);
        dbInfoVo.setVersion(version);
        return dbInfoVo;
    }

    //3. 获取数据库表信息
    @Override
    public List<DbTableInfoVo> getDbTableInfo() throws SQLException, ClassNotFoundException, ParseException {
        //获取所有表
        String tableSql = "select table_name as tableName, \n" +
                "   engine, \n" +
                "   table_comment as tableComment, \n" +
                "   table_collation as tableCollation, \n" +
                "   create_time as createTime \n" +
                "from information_schema.tables\n" +
                "where table_schema = (select database())\n" +
                "";
        //order by create_time desc

      List<Map<String, Object>> dbTableList  = JdbcConnect.mysqlDbQuery(tableSql);

      //日期格式化
      SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
      List<DbTableInfoVo> dbTableInfoVoList = new ArrayList<>();
        for (Map<String, Object> dbTable : dbTableList){

            DbTableInfoVo dbTableInfoVo = new DbTableInfoVo();
            dbTableInfoVo.setTableName(dbTable.getOrDefault("TABLE_NAME","").toString());
            dbTableInfoVo.setEngine(StrUtils.valueOf(dbTable.getOrDefault("ENGINE","")));
            dbTableInfoVo.setTableComment(dbTable.getOrDefault("TABLE_COMMENT","").toString());
            dbTableInfoVo.setTableCollation(StrUtils.valueOf(dbTable.getOrDefault("TABLE_COLLATION","")));
            dbTableInfoVo.setTableCollation(StrUtils.valueOf(dbTable.getOrDefault("TABLE_COLLATION","")));
            dbTableInfoVo.setCreateTime(StrUtils.valueOf(dbTable.getOrDefault("CREATE_TIME","")).replace("T",""));
            dbTableInfoVoList.add(dbTableInfoVo);
        }
        return dbTableInfoVoList;
    }
    //4. 获取数据库表结构信息
    @Override
    public List<DbTableStructureInfoVo> getDbTableStructureInfo() throws SQLException, ClassNotFoundException {

        //获取所有表结构
        //查询条件 #table_name = '表名' table_schema = (数据库名)
        String tableStructureSql = "select column_name, \n" +
                "\ttable_name,\n" +
                "   data_type, \n" +
                "   column_comment, \n" +
                "   column_key , \n" +
                "   is_nullable ,\n" +
                "   column_type ,\n" +
                "   column_default ,\n"+
                "\t table_schema \n" +
                "from information_schema.columns\n" +
                "where \n" +
                "   table_schema = (select database()) \n" +
                "order by ordinal_position;";

        List<Map<String, Object>> dbTableStructureList  = JdbcConnect.mysqlDbQuery(tableStructureSql);

        List<DbTableStructureInfoVo> dbTableStructureVoList = new ArrayList<>();
        for (Map<String,Object>  dbTableStructure:dbTableStructureList){
            DbTableStructureInfoVo dbTableStructureVo = new DbTableStructureInfoVo();
            dbTableStructureVo.setTableSchema(dbTableStructure.getOrDefault("TABLE_SCHEMA","").toString());
            dbTableStructureVo.setTableName(dbTableStructure.getOrDefault("TABLE_NAME","").toString());
            dbTableStructureVo.setColumnName(dbTableStructure.getOrDefault("COLUMN_NAME","").toString());
            dbTableStructureVo.setDataType(dbTableStructure.getOrDefault("DATA_TYPE","").toString());
            dbTableStructureVo.setColumnComment(StrUtils.valueOf(dbTableStructure.getOrDefault("COLUMN_COMMENT","")));
            dbTableStructureVo.setColumnDefault(StrUtils.valueOf(dbTableStructure.getOrDefault("COLUMN_DEFAULT","")));
            dbTableStructureVo.setColumnKey(StrUtils.valueOf(dbTableStructure.getOrDefault("COLUMN_KEY","")));
            dbTableStructureVo.setIsNullable(dbTableStructure.getOrDefault("IS_NULLABLE","").toString());
            dbTableStructureVo.setColumnType(dbTableStructure.getOrDefault("COLUMN_TYPE","").toString());

            dbTableStructureVoList.add(dbTableStructureVo);
        }


        return dbTableStructureVoList;
    }

    @Override
    public DbTableDocumentVo getDataSoreceTableInfo() throws SQLException, ClassNotFoundException, IOException {

        //配置信息
        String path =  System.getProperty("user.dir")+"/src/main/resources/properties/"+dbProperties;
        File file = new File(path);
        FileInputStream fileInputStream = new FileInputStream(file);
        Properties properties = new Properties();
        properties.load(fileInputStream);

        //数据源
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName(properties.get("driver").toString());
        hikariConfig.setJdbcUrl(properties.get("url").toString());
        hikariConfig.setUsername(properties.get("username").toString());
        hikariConfig.setPassword(properties.get("password").toString());
        //设置可以获取tables remarks信息
        hikariConfig.addDataSourceProperty("useInformationSchema", "true");
        hikariConfig.setMinimumIdle(2);
        hikariConfig.setMaximumPoolSize(5);
        DataSource dataSource = new HikariDataSource(hikariConfig);
        Connection connection = dataSource.getConnection();

        //数据库版本号
        String databaseProductVersion = connection.getMetaData().getDatabaseProductVersion();
        //数据库名称
        String cataLog =  connection.getCatalog();
        //数据库架构
        String schema =connection.getSchema();

        //获取表信息
        ResultSet tables = connection.getMetaData().getTables(cataLog, schema, "%", new String[]{"TABLE"});
        int columnCount = tables.getMetaData().getColumnCount();
        //存放列名和结果
        List<Map<String, Object>> values = new ArrayList<>(16);
        while (tables.next()){
            Map<String,Object> map = new HashMap<>();
            for (int i=1;i<=columnCount;i++){
                map.put(tables.getMetaData().getColumnName(i),tables.getObject(i));
            }
            values.add(map);
        }

        //获取表信息
        List<DbTableInfoVo> dbTableInfoVoList = DataSoreceConnect.getTable(properties.get("url").toString(), values);
        //获取表结构信息
        List<DbTableStructureInfoVo> dbTableStructureInfoVoList = DataSoreceConnect.getTableStructure(properties.get("url").toString(),connection);

        //拼装数据
        dbTableInfoVoList.stream().map(item->{
            item.setDbTableStructureVoList(dbTableStructureInfoVoList.stream().filter(filter -> filter.getTableName().equals(item.getTableName())).collect(Collectors.toList()));
            return item;
        }).collect(Collectors.toList());

        //数据库信息
        DbInfoVo dbInfoVo = new DbInfoVo();
        dbInfoVo.setDbName(cataLog);
        dbInfoVo.setVersion(databaseProductVersion);

        DbTableDocumentVo dbTableDocumentVo = new DbTableDocumentVo();
        dbTableDocumentVo.setDbInfoVo(dbInfoVo);
        dbTableDocumentVo.setDbTableInfoVoList(dbTableInfoVoList);


        DataSoreceConnect.close(connection);

        return dbTableDocumentVo;
    }


}
